package jc.malpractice.service;

import jc.malpractice.db.DbUtil;
import jc.malpractice.db.MsSqlUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CronService {
    //malpractice_patient_info表数据转换
    public static void itmmastSync() {
        System.out.println("病人信息表被更新");
        Connection Ms_connection =null;
        Connection MySql_connection=null;
        Statement Ms_statement=null;
        Statement MySql_statement=null;
        ResultSet Ms_resultSet=null;
        try {
            //sqlser连接池

            //mysql连接池
            MySql_connection = DbUtil.getConn();
            Ms_connection = MsSqlUtil.mssqlSource.getConnection();
            Ms_statement = Ms_connection.createStatement();
            MySql_statement = MySql_connection.createStatement();
            String sql = "SELECT * FROM [dbo].[malpractice_patient_info]";
            Ms_resultSet = Ms_statement.executeQuery(sql);

            StringBuilder stringBuilder = new StringBuilder();

//            int i = 0;
            while (Ms_resultSet.next()) {
                stringBuilder.append("('");
                stringBuilder.append(Ms_resultSet.getString("in_his_no") + "','");
                stringBuilder.append(Ms_resultSet.getString("out_his_no") + "','");
                stringBuilder.append(Ms_resultSet.getString("name") + "','");
                stringBuilder.append(Ms_resultSet.getString("gender") + "','");
                stringBuilder.append(Ms_resultSet.getString("age") + "','");
                stringBuilder.append(Ms_resultSet.getString("nationality") + "','");
                stringBuilder.append(Ms_resultSet.getString("weight") + "','");
                stringBuilder.append(Ms_resultSet.getString("contactinfo") + "','");
                stringBuilder.append(Ms_resultSet.getString("in_time") + "','");
                stringBuilder.append(Ms_resultSet.getString("surgery_time") + "','");
                stringBuilder.append(Ms_resultSet.getString("diagnosis") + "','");
                stringBuilder.append(Ms_resultSet.getString("doc_in_charge") + "','");
                stringBuilder.append(Ms_resultSet.getString("blood_type") + "','");
                stringBuilder.append(Ms_resultSet.getString("pregnancy_info") + "','");
                stringBuilder.append(check(Ms_resultSet.getString("blood_transfusion_info")));
                stringBuilder.append("'),");
//                i++;
            }

            stringBuilder.deleteCharAt(stringBuilder.length() - 1);

            sql = "INSERT IGNORE INTO event_patient(in_his_no,out_his_no,pname,gender,age,nationality,weight,contactinfo,in_time,surgery_time,diagnosis,doc_in_charge,blood_type,pregnancy_info,blood_transfusion_info) VALUES ";
            MySql_statement.execute("TRUNCATE event_patient");
            MySql_statement.execute(sql + stringBuilder.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (Ms_resultSet != null) Ms_resultSet.close();
                if (MySql_statement != null) MySql_statement.close();
                if (Ms_statement != null) Ms_statement.close();
                MySql_connection.close();
                Ms_connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //malpractice_dept表数据转换
    public static void itmmastSync1() {
        System.out.println("部门信息表被更新");
        Connection Ms_connection =null;
        Connection MySql_connection=null;
        Statement Ms_statement=null;
        Statement MySql_statement=null;
        ResultSet Ms_resultSet=null;
        try {
            //sqlser连接池
            Ms_connection = MsSqlUtil.mssqlSource.getConnection();
            //mysql连接池
            MySql_connection = DbUtil.getConn();
            Ms_statement = Ms_connection.createStatement();
            MySql_statement = MySql_connection.createStatement();

            String sql = "SELECT * FROM [dbo].[malpractice_dept]";
            Ms_resultSet = Ms_statement.executeQuery(sql);

            StringBuilder stringBuilder = new StringBuilder();

//            int i = 0;
            while (Ms_resultSet.next()) {
                stringBuilder.append("('");
                stringBuilder.append(Ms_resultSet.getString("dept_code") + "','");
                stringBuilder.append(check(Ms_resultSet.getString("dept_name")));
                stringBuilder.append("'),");
//                i++;
            }

            stringBuilder.deleteCharAt(stringBuilder.length() - 1);

            sql = "INSERT IGNORE INTO event_dept(dept_code,dept_name) VALUES ";
            MySql_statement.execute("TRUNCATE event_dept");
            MySql_statement.execute(sql + stringBuilder.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (Ms_resultSet != null) Ms_resultSet.close();
                if (MySql_statement != null) MySql_statement.close();
                if (Ms_statement != null) Ms_statement.close();
                MySql_connection.close();
                Ms_connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //malpractice_user表数据转换
    public static void itmmastSync2() {
        System.out.println("用户信息表被更新");
        Connection Ms_connection =null;
        Connection MySql_connection=null;
        Statement Ms_statement=null;
        Statement MySql_statement=null;
        ResultSet Ms_resultSet=null;
        try {
            //sqlser连接池
            Ms_connection = MsSqlUtil.mssqlSource.getConnection();
            //mysql连接池
            MySql_connection = DbUtil.getConn();
            Ms_statement = Ms_connection.createStatement();
            MySql_statement = MySql_connection.createStatement();
            String sql = "SELECT * FROM [dbo].[malpractice_user]";
            Ms_resultSet = Ms_statement.executeQuery(sql);

            StringBuilder stringBuilder = new StringBuilder();

//            int i = 0;
            while (Ms_resultSet.next()) {
                stringBuilder.append("('");
                stringBuilder.append(Ms_resultSet.getString("user_code") + "','");
                stringBuilder.append(Ms_resultSet.getString("user_name") + "','");
                String dept_codes = Ms_resultSet.getString("dept_codes");
                if(dept_codes == null) {
                    dept_codes = "0";
                }
                stringBuilder.append(check(dept_codes));
                stringBuilder.append("'),");
//                i++;
            }

            stringBuilder.deleteCharAt(stringBuilder.length() - 1);

            sql = "INSERT IGNORE INTO reporter(reporter_code,reporter_name,dept_code) VALUES ";
            MySql_statement.execute("TRUNCATE reporter");
            MySql_statement.execute(sql + stringBuilder.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (Ms_resultSet != null) Ms_resultSet.close();
                if (MySql_statement != null) MySql_statement.close();
                if (Ms_statement != null) Ms_statement.close();
                MySql_connection.close();
                Ms_connection.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    static String check(String s) {
        if (s.contains("'")) {
            StringBuilder stringBuilder = new StringBuilder(s);
            stringBuilder.insert(s.indexOf("'"), "\\");
            return stringBuilder.toString();
        }
        return s;
    }
}